'''cdr_db_mysql'''

import mysql.connector
import re
import datetime

config = {
	'DEF_DB_HOST': 'localhost', 
	'DEF_DB_USER': 'root', 
	'DEF_DB_PASS': 'foo', 
	'DEF_DB_NAME': 'biller' 
}

class cdrMySQL:
	"""docstring for cdrMysql"""
	_db = None

	def __init__(self, db_host=config['DEF_DB_HOST'], db_user=config['DEF_DB_USER'], db_passwd=config['DEF_DB_PASS'], db=config['DEF_DB_NAME']):
		self._db = mysql.connector.connect(user=db_user, password=db_passwd, host=db_host, database=db)
		self._db.autocommit = True
		
	def close(self):
		self._db.close()

	def cdr_wr(self, cdr):
		ocg = cdr["ocg"]
		fcd = cdr["fcd"]
		time_orig = cdr["t_orig"]
		time_con = cdr["t_con"]
		duration = cdr["dur"]
		money = cdr["money"]

		cur = self._db.cursor()
		q_vars = (ocg, fcd, time_orig, time_con, duration, money)
		sql_str = "INSERT INTO cdrs (ocg, ocd, clock_org, clock_con, duration, money) "+\
			"VALUES (%s, %s, %s, %s, %s, %s)"
		cur.execute(sql_str, q_vars)
		cur.close()

	def cdr_rd(self, filter=None):
		cur = self._db.cursor()
		sql_str = "SELECT cdr_id, ocg, ocd, clock_org, clock_con, duration, money FROM cdrs"
		if filter!=None:
			sql_str += " WHERE "+filter
		cur.execute(sql_str)

		rt = cur.fetchall()
		cur.close()
		return rt

	def rate_rd(self, cld_ptrn_id, clg_ptrn_id, tr_id):
		cur = self._db.cursor()
		sql_str = "SELECT money from rate WHERE clg_ptrn_id=%s AND cld_ptrn_id=%s AND tr_id=%s"
		cur.execute(sql_str, (clg_ptrn_id, cld_ptrn_id, tr_id))

		rt = cur.fetchone()
		if rt!=None:
			rt = rt[-1] #money temp workaround
		else:
			rt = self._get_def_rate()
		cur.close()
		return rt

	def rate_wr(self, cld_ptrn_id, clg_ptrn_id, tr_id, money):
		cur = self._db.cursor()
		sql_str = "SELECT money from rate WHERE clg_ptrn_id=%s AND cld_ptrn_id=%s AND tr_id=%s"
		cur.execute(sql_str, (clg_ptrn_id, cld_ptrn_id, tr_id))

		if len(cur.fetchall())!=0:
			sql_str = "UPDATE rate SET money=%s WHERE clg_ptrn_id=%s AND cld_ptrn_id=%s AND tr_id=%s"
		else:
			sql_str = "INSERT INTO rate(money, clg_ptrn_id, cld_ptrn_id, tr_id) VALUES (%s, %s, %s, %s)"

		cur.execute(sql_str, (money, clg_ptrn_id, cld_ptrn_id, tr_id))
		cur.close()

	def match_pattern(self, num):
		cur = self._db.cursor()
		#check if there's a pattern that fully represents num
		sql_str = "SELECT * FROM num_patterns WHERE pattern=%s"
		tup = (num,)
		cur.execute(sql_str, tup)
		res = cur.fetchall()
		if len(res) > 0:
			rt = res[0][0] #ptrn_id temp workaround
			cur.close()
			return rt
		else:
			#check for pattern that matches num
			sql_str = "SELECT * FROM num_patterns WHERE pattern!='*'"
			cur.execute(sql_str)
			for row in cur.fetchall():
				match_obj = re.match(row[1], num) #pattern temp workaround
				if match_obj and match_obj.group(0)==num:
					rt = row[0] #ptrn_id temp workaround
					return rt
			#if execution reaches here, then no match is found above, match with the *
			sql_str = "SELECT * FROM num_patterns WHERE pattern='*'"
			cur.execute(sql_str)
			rt = cur.fetchone()[0] #ptrn_id temp workaround
			cur.close()
			return rt

	def match_time_range(self, time):
		cur = self._db.cursor()
		#currently the only time period is forever!
		sql_str = "SELECT * FROM time_ranges WHERE tr_start=0 AND tr_end=0"
		cur.execute(sql_str)
		rt = cur.fetchone()[0]
		cur.close()
		return rt

	def pattern_wr(self, pattern, pattern_desc=""):
		cur = self._db.cursor()
		#if the pattern exists do nothing
		sql_str = "SELECT * FROM num_patterns WHERE pattern=%s"
		cur.execute(sql_str, (pattern,))
		if len(cur.fetchall())==0:
			sql_str = "INSERT INTO num_patterns(pattern, ptrn_desc) VALUES(%s, %s)"
			cur.execute(sql_str, (pattern, pattern_desc))
		cur.close();

	def _get_def_rate(self):
		cur = self._db.cursor()
		sql_str = "SELECT ptrn_id from num_patterns WHERE pattern='*'"
		cur.execute(sql_str)
		num_ptrn = cur.fetchone()[0]
		sql_str = "SELECT tr_id from time_ranges WHERE tr_start=0 AND tr_end=0"
		cur.execute(sql_str)
		t_id = cur.fetchone()[0]

		sql_str = "SELECT money FROM rate WHERE clg_ptrn_id=%s AND cld_ptrn_id=%s AND tr_id=%s"
		cur.execute(sql_str, (num_ptrn, num_ptrn, t_id))
		rt = cur.fetchone()[0]
		cur.close()
		return rt

if __name__ == '__main__':
	my = cdrMySQL()
	print my.match_pattern('0322362407')
